package jsyf.oa.dao.contract;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.entity.contract.RepaymentEntity;
import jsyf.oa.entity.system.CompanyEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository
public class RepaymentDao extends BaseDao {

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, RepaymentEntity.class);
    }

    public RepaymentDao() {
        super.iBaseEntity = new RepaymentEntity();
    }

    public List<JSONObject> getrepaymentList(String serialNo) {
        String sql = "SELECT *\n" +
                "FROM t_con_repayment\n" +
                "WHERE t_con_repayment.CONTRACT_NO='" + serialNo + "'";
        return queryForJsonList(sql);
    }
    public List<RepaymentEntity> getByPageIBaseEntity(String condition, Object[] params, int requestPage, int pageSize) {
        List<RepaymentEntity> list = new ArrayList<>();
        List<IBaseEntity> iBaseEntityList = super.getIBaseEntityListByPage(condition, params, requestPage, pageSize);
        for (int i = 0; i < iBaseEntityList.size(); i++) {
            list.add((RepaymentEntity) iBaseEntityList.get(i));
        }
        return list;
    }

    public List<RepaymentEntity> getByConditionIBaseEntity(String condition, Object[] params) {
        return getByPageIBaseEntity(condition, params, 1, Integer.MAX_VALUE);
    }
    public List<JSONObject> getAcountRepaymentList(String mainId,String type) {
        String sql = "SELECT\n" +
                "t_con_repayment.ID AS id,\n" +
                "t_con_repayment.MAIN_ID AS mainId,\n" +
                "t_con_repayment.REPAYMENT_DATE AS repaymentDate,\n" +
                "t_con_repayment.REMAINING_ARREARS AS remainingArrears,\n" +
                "t_con_repayment.INTEREST_FREE_FLAG AS interestFreeFlag,\n" +
                "t_con_repayment.INTEREST_FREE_REASON AS interestFreeReason,\n" +
                "t_con_repayment.INTEREST_FREE_ATTACH_URL AS interestFreeAttachUrl,\n" +
                "t_con_repayment.INTEREST_FREE_ATTACH_NAME AS interestFreeAttachName,\n" +
                "t_con_repayment.FREE_MONEY AS freeMoney,\n" +
                "t_con_repayment.CD_OVERDUE_PENALTY AS cdOverduePenalty,\n" +
                "t_con_repayment.CD_REMAINING_ARREARS AS cdRemainingArrears,\n" +
                "t_con_repayment.CREATE_TIME AS createTime,\n" +
                "t_con_repayment.TOTAL AS total,\n" +
                "t_con_repayment.HK_TYPE AS hkType,\n" +
                "t_con_repayment.PRINCIPAL AS principal,\n" +
                "t_con_repayment.REMARK AS remark,\n" +
                "t_con_repayment.INTEREST AS interest,\n" +
                "t_con_repayment.GS_PENALTY AS gsPenalty,\n" +
                "t_con_repayment.CREATE_USER_ID AS createUserId,\n" +
                "t_con_repayment.CREATE_USER_NAME AS createUserName,\n" +
                "t_con_repayment.CREATE_DATE AS createDate,\n" +
                "t_con_repayment.CREATE_TIME AS createTime,\n" +
                "t_con_repayment.TYPE AS type,\n" +
                "t_con_repayment.REPAYMENT_ATTACH_NAME AS repaymentAttachName,\n" +
                "t_con_repayment.REPAYMENT_ATTACH_URL AS repaymentAttachUrl,\n" +
                "t_con_repayment.ACTUAL_REPAYMENT_DATE AS actualRepaymentDate,\n" +
                "t_con_repayment.REPAYMENT_AMOUNT AS repaymentAmount,\n" +
                "t_con_repayment.OVERDUE_PENALTY AS overduePenalty,\n" +
                "t_con_repayment.CONTRACT_REPAYMENT_AMOUNT AS contractRepaymentAmount,\n" +
                "'0.00' AS qkRepayment,\n" +
                "'0.00' AS yqRepayment,\n" +
                "sumMoney.sumqkmoney ,\n" +
                "sumMoney.sumyqmoney\n" +
                "FROM\n" +
                "\tt_con_repayment\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_con_repayment.MAIN_ID AS mainId,\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE AS repaymentDate,\n" +
                "\t\tSUM(REMAINING_ARREARS) AS sumqkmoney,\n" +
                "\t\tSUM(OVERDUE_PENALTY) AS sumyqmoney\n" +
                "\tFROM\n" +
                "\t\t`t_con_repayment`\n" +
                "\tWHERE\n" +
                "\t\tt_con_repayment.MAIN_ID = '"+mainId+"'\n" +
                "\tAND t_con_repayment.TYPE = '"+type+"'\n" +
                "\tGROUP BY\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\tORDER BY\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                ") AS sumMoney ON t_con_repayment.MAIN_ID = sumMoney.mainId\n" +
                "AND t_con_repayment.REPAYMENT_DATE = sumMoney.repaymentDate\n" +
                "WHERE\n" +
                "\tt_con_repayment.MAIN_ID = '"+mainId+"'\n" +
                "AND t_con_repayment.TYPE = '"+type+"'\n" +
                "AND PARENT_ID = '0'\n" +
                "AND (t_con_repayment.REMAINING_ARREARS !='0.00' \n" +
                "OR t_con_repayment.OVERDUE_PENALTY!='0.00' \n" +
                "OR\n" +
                "t_con_repayment.REPAYMENT_DATE =(SELECT REPAYMENT_DATE FROM t_con_repayment \n" +
                "WHERE (REPAYMENT_DATE >= CURDATE())\n" +
                "AND t_con_repayment.TYPE = '"+type+"'\n" +
                "AND t_con_repayment.MAIN_ID = '"+mainId+"'\n" +
                "ORDER BY REPAYMENT_DATE LIMIT 1)\n" +
                " )\n" +
                " OR\n" +
                "ID in(SELECT PARENT_ID FROM t_con_repayment \n" +
                "WHERE t_con_repayment.ACTUAL_REPAYMENT_DATE=CURDATE() \n" +
                "AND t_con_repayment.MAIN_ID = '"+mainId+"'" +
                "AND TYPE='"+type+"')\n" +
                "OR \n" +
                "ID in(SELECT ID FROM t_con_repayment \n" +
                "WHERE t_con_repayment.ACTUAL_REPAYMENT_DATE=CURDATE() \n" +
                "AND t_con_repayment.MAIN_ID = '"+mainId+"'\n" +
                "AND t_con_repayment.PARENT_ID = '0'" +
                "AND TYPE='"+type+"')" +
                "ORDER BY\n" +
                "\tt_con_repayment.REPAYMENT_DATE";
        return queryForJsonList(sql);
    }

    public List<JSONObject> getRepaymentDate() {
        String sql = "SELECT\n" +
                "\tt_con_main.ID AS mainId,\n" +
                "\tt_con_main.BUYER AS buyer\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND (\n" +
                "\tto_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) = 5\n" +
                "\tOR to_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) = 1\n" +
                ")\n" +
                "AND (\n" +
                "\tt_con_repayment.ACTUAL_REPAYMENT_DATE IS NULL\n" +
                "\tOR t_con_repayment.ACTUAL_REPAYMENT_DATE < t_con_repayment.REPAYMENT_DATE\n" +
                ")\n" +
                "AND t_con_main.HAVE_DSDF = '0'\n" +
                "AND t_con_main.BUY_TYPE = '内部分期'\n" +
                "AND (\n" +
                "\tt_con_repayment.TYPE = '3'\n" +
                "\tOR t_con_repayment.TYPE = '1'\n" +
                "\tOR t_con_repayment.TYPE = '0'\n" +
                ")\n" +
                "UNION\n" +
                "\tSELECT\n" +
                "\t\tt_con_main.ID AS mainId,\n" +
                "\t\tt_con_main.BUYER AS buyer\n" +
                "\tFROM\n" +
                "\t\tt_con_main\n" +
                "\tLEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND (\n" +
                "\t\tto_days(\n" +
                "\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t) - to_days(now()) = 5\n" +
                "\t\tOR to_days(\n" +
                "\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t) - to_days(now()) = 1\n" +
                "\t)\n" +
                "\tAND (\n" +
                "\t\tt_con_repayment.ACTUAL_REPAYMENT_DATE IS NULL\n" +
                "\t\tOR t_con_repayment.ACTUAL_REPAYMENT_DATE < t_con_repayment.REPAYMENT_DATE\n" +
                "\t)\n" +
                "\tAND t_con_main.BUY_TYPE != '内部分期'\n" +
                "\tAND t_con_main.HAVE_DSDF = '0'\n" +
                "\tAND (\n" +
                "\t\tt_con_repayment.TYPE = '3'\n" +
                "\t\tOR t_con_repayment.TYPE = '1'\n" +
                "\t)\n" +
                "\tUNION\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_con_main.ID AS mainId,\n" +
                "\t\t\tt_con_main.BUYER AS buyer\n" +
                "\t\tFROM\n" +
                "\t\t\tt_con_main\n" +
                "\t\tLEFT JOIN t_con_financingment ON t_con_main.ID = t_con_financingment.MAIN_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND (\n" +
                "\t\t\tto_days(\n" +
                "\t\t\t\tt_con_financingment.FINANCING_DATE\n" +
                "\t\t\t) - to_days(now()) = 5\n" +
                "\t\t\tOR to_days(\n" +
                "\t\t\t\tt_con_financingment.FINANCING_DATE\n" +
                "\t\t\t) - to_days(now()) = 1\n" +
                "\t\t)\n" +
                "\t\tAND t_con_main.HAVE_DSDF = '0'\n" +
                "\t\tAND t_con_main.BUY_TYPE != '内部分期'\n" +
                "\t\tUNION\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_con_main.ID AS mainId,\n" +
                "\t\t\t\tt_con_main.BUYER AS buyer\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_con_main\n" +
                "\t\t\tLEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\tto_days(\n" +
                "\t\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t\t) - to_days(now()) = 5\n" +
                "\t\t\t\tOR to_days(\n" +
                "\t\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t\t) - to_days(now()) = 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\tt_con_repayment.ACTUAL_REPAYMENT_DATE IS NULL\n" +
                "\t\t\t\tOR t_con_repayment.ACTUAL_REPAYMENT_DATE < t_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t)\n" +
                "\t\t\tAND t_con_main.HAVE_DSDF = '1'";
        return queryForJsonList(sql);
    }

    public List<JSONObject> getRepaymentDateBefore() {
        String sql = "SELECT\n" +
                "\tt_con_main.ID AS mainId,\n" +
                "\tt_con_main.BUYER AS buyer\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND (\n" +
                "\tto_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) = - 1\n" +
                ")\n" +
                "AND t_con_main.HAVE_DSDF = '0'\n" +
                "AND t_con_main.AUDIT_FLAG = '1'\n" +
                "AND t_con_main.BUY_TYPE = '内部分期'\n" +
                "AND (\n" +
                "\tt_con_repayment.TYPE = '3'\n" +
                "\tOR t_con_repayment.TYPE = '1'\n" +
                "\tOR t_con_repayment.TYPE = '0'\n" +
                ")\n" +
                "UNION\n" +
                "\tSELECT\n" +
                "\t\tt_con_main.ID AS mainId,\n" +
                "\t\tt_con_main.BUYER AS buyer\n" +
                "\tFROM\n" +
                "\t\tt_con_main\n" +
                "\tLEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND (\n" +
                "\t\tto_days(\n" +
                "\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t) - to_days(now()) = - 1\n" +
                "\t)\n" +
                "\tAND t_con_main.BUY_TYPE != '内部分期'\n" +
                "\tAND t_con_main.HAVE_DSDF = '0'\n" +
                "\tAND t_con_main.AUDIT_FLAG = '1'\n" +
                "\tAND (\n" +
                "\t\tt_con_repayment.TYPE = '3'\n" +
                "\t\tOR t_con_repayment.TYPE = '1'\n" +
                "\t)\n" +
                "\tUNION\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_con_main.ID AS mainId,\n" +
                "\t\t\tt_con_main.BUYER AS buyer\n" +
                "\t\tFROM\n" +
                "\t\t\tt_con_main\n" +
                "\t\tLEFT JOIN t_con_financingment ON t_con_main.ID = t_con_financingment.MAIN_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND (\n" +
                "\t\t\tto_days(\n" +
                "\t\t\t\tt_con_financingment.FINANCING_DATE\n" +
                "\t\t\t) - to_days(now()) = - 1\n" +
                "\t\t)\n" +
                "\t\tAND t_con_main.HAVE_DSDF = '0'\n" +
                "\t\tAND t_con_main.AUDIT_FLAG = '1'\n" +
                "\t\tAND t_con_main.BUY_TYPE != '内部分期'\n" +
                "\t\tUNION\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_con_main.ID AS mainId,\n" +
                "\t\t\t\tt_con_main.BUYER AS buyer\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_con_main\n" +
                "\t\t\tLEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND t_con_main.AUDIT_FLAG = '1'\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\tto_days(\n" +
                "\t\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t\t) - to_days(now()) = - 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND t_con_main.HAVE_DSDF = '1'";
        return queryForJsonList(sql);
    }

    public List<JSONObject> interestLook(String userId) {
        String sql = "SELECT\n" +
                "\tMAIN_ID AS mainId\n" +
                "FROM\n" +
                "\tt_sys_audit\n" +
                "WHERE\n" +
                "\tAUDITOR_ID = '"+userId+"'\n" +
                "AND MODULE_NAME = 'interestFree'\n" +
                "UNION\n" +
                "\tSELECT\n" +
                "\t\tMAIN_ID AS mainId\n" +
                "\tFROM\n" +
                "\t\tt_sys_copier\n" +
                "\tWHERE\n" +
                "\t\tCOPIER_ID = '"+userId+"'\n" +
                "\tAND MODULE_NAME = 'interestFree'";
        return queryForJsonList(sql);
    }

    public JSONObject getRepaymentSender(String mainId) {
        String sql = "SELECT\n" +
                "\tt_con_main.BUYER AS customerName,\n" +
                "\tt_con_main.BUYER_TEL AS customerTel,\n" +
                "\tt_con_main.BUYER_TAX_NO AS buyerTaxNo,\n" +
                "\tt_con_main.BUY_TYPE AS buyType,\n" +
                "\tt_con_main.BRAND_NAME AS brandName,\n" +
                "\tt_con_main.MODEL_NAME AS modelName,\n" +
                "\tt_con_main.HAVE_DSDF AS haveDsdf,\n" +
                "\tt_con_main.REGION_MANAGER_ID AS regionManagerId,\n" +
                "\tt_con_main.BIG_REGION_MANAGER_ID AS bigRegionManagerId,\n" +
                "\tt_con_main.REGIONAL_SUPPORT_ID AS regionalSupportId,\n" +
                "\tt_con_main.CREATE_USER_ID AS createUserId,\n" +
                "\tt_con_main.CREDIT_MANAGER_ID AS creditManagerId,\n" +
                "\tt_con_main.DATA_OFFICER_ID AS dataOfficerId,\n" +
                "\tt_con_main.TAKE_OVER_MANAGER_ID AS takeOverManagerId,\n" +
                "\tt_con_main.DEVICE_TYPE AS deviceName,\n" +
                "\tt_sys_company.`NAME` AS companyName,\n" +
                "t_con_repayment.REPAYMENT_DATE AS repaymentDate,\n" +
                "IFNULL(t_con_main.SUP_REPAY_TOTAL_AMOUNT,'0.00') AS totalCustomerArrears,\n" +
                "IFNULL(t_con_main.ARREARS_REPAYMENT_AMOUNT,'0.00') AS arrearsRepaymentAmount,\n" +
                "IFNULL(t_con_main.ACCUMULATED_REPAYMENT_AMOUNT,'0.00') AS accumulatedRepaymentAmount,\n" +
                "CASE\n" +
                "WHEN t_con_main.BUY_TYPE='全款'||t_con_main.BUY_TYPE='内部分期'\n" +
                "THEN t_con_sales.TOTAL_PRICE\n" +
                "ELSE\n" +
                "t_con_sales.UNIT_PRICE\n" +
                "END AS totalPrice\t,\n" +
                "CASE\n" +
                "WHEN t_con_main.BRAND_NAME='约翰迪尔'\n" +
                "THEN SUBSTRING(t_con_main.SERIAL_NO, -6) \n" +
                "WHEN t_con_main.BRAND_NAME='利勃海尔'\n" +
                "THEN SUBSTRING(t_con_main.SERIAL_NO, -5)\n" +
                "WHEN t_con_main.BRAND_NAME='凯斯'\n" +
                "THEN SUBSTRING(t_con_main.SERIAL_NO, -4)\t\n" +
                "END AS serialNo\t\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_con_sales ON t_con_main.ID = t_con_sales.MAIN_ID\n" +
                "LEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "LEFT JOIN t_sys_company ON t_con_main.SELLER_COMPANY_ID =t_sys_company.ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND (\n" +
                "\tto_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) >= 0\n" +
                "\tAND to_days(\n" +
                "\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t) - to_days(now()) <= 5\n" +
                ")\n" +
                "AND t_con_main.ID='" + mainId + "'\n" +
                "AND (t_con_repayment.ACTUAL_REPAYMENT_DATE IS NULL\n" +
                " OR t_con_repayment.ACTUAL_REPAYMENT_DATE<t_con_repayment.REPAYMENT_DATE)\n" +
                "GROUP BY t_con_main.ID";
        return queryForJsonObject(sql);
    }

    public JSONObject getRepaymentSender(String mainId,String months) {
        String sql = "SELECT\n" +
                "\tt_con_main.BUYER AS customerName,\n" +
                "\tt_con_main.BUYER_TEL AS customerTel,\n" +
                "\tt_con_main.BUYER_TAX_NO AS buyerTaxNo,\n" +
                "\tt_con_main.BUY_TYPE AS buyType,\n" +
                "\tt_con_main.BRAND_NAME AS brandName,\n" +
                "\tt_con_main.MODEL_NAME AS modelName,\n" +
                "\tt_con_main.HAVE_DSDF AS haveDsdf,\n" +
                "\tt_con_main.REGION_MANAGER_ID AS regionManagerId,\n" +
                "\tt_con_main.BIG_REGION_MANAGER_ID AS bigRegionManagerId,\n" +
                "\tt_con_main.REGIONAL_SUPPORT_ID AS regionalSupportId,\n" +
                "\tt_con_main.CREATE_USER_ID AS createUserId,\n" +
                "\tt_con_main.CREDIT_MANAGER_ID AS creditManagerId,\n" +
                "\tt_con_main.DATA_OFFICER_ID AS dataOfficerId,\n" +
                "\tt_con_main.TAKE_OVER_MANAGER_ID AS takeOverManagerId,\n" +
                "\tt_con_main.DEVICE_TYPE AS deviceName,\n" +
                "\tt_sys_company.`NAME` AS companyName,\n" +
                "t_con_repayment.REPAYMENT_DATE AS repaymentDate,\n" +
                "IFNULL(t_con_main.SUP_REPAY_TOTAL_AMOUNT,'0.00') AS totalCustomerArrears,\n" +
                "IFNULL(t_con_main.ARREARS_REPAYMENT_AMOUNT,'0.00') AS arrearsRepaymentAmount,\n" +
                "IFNULL(t_con_main.ACCUMULATED_REPAYMENT_AMOUNT,'0.00') AS accumulatedRepaymentAmount,\n" +
                "CASE\n" +
                "WHEN t_con_main.BUY_TYPE='全款'||t_con_main.BUY_TYPE='内部分期'\n" +
                "THEN t_con_sales.TOTAL_PRICE\n" +
                "ELSE\n" +
                "t_con_sales.UNIT_PRICE\n" +
                "END AS totalPrice\t,\n" +
                "CASE\n" +
                "WHEN t_con_main.BRAND_NAME='约翰迪尔'\n" +
                "THEN SUBSTRING(t_con_main.SERIAL_NO, -6) \n" +
                "WHEN t_con_main.BRAND_NAME='利勃海尔'\n" +
                "THEN SUBSTRING(t_con_main.SERIAL_NO, -5)\n" +
                "WHEN t_con_main.BRAND_NAME='凯斯'\n" +
                "THEN SUBSTRING(t_con_main.SERIAL_NO, -4)\t\n" +
                "END AS serialNo\t\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_con_sales ON t_con_main.ID = t_con_sales.MAIN_ID\n" +
                "LEFT JOIN t_con_repayment ON t_con_main.ID = t_con_repayment.MAIN_ID\n" +
                "LEFT JOIN t_sys_company ON t_con_main.SELLER_COMPANY_ID =t_sys_company.ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_con_repayment.REPAYMENT_DATE LIKE '%"+months+"%'\n" +
                "AND t_con_main.ID='" + mainId + "'\n" +
                "GROUP BY t_con_main.ID";
        return queryForJsonObject(sql);
    }

    public JSONObject getRepaymentAmount(String repayDate, String mainId) {
        String sql = "SELECT\n" +
                "\trepayment.total,repayment.principal,\n" +
                "REPAYMENT_AMOUNT AS repaymentAmount,\n" +
                "CASE WHEN SUM(REPAYMENT_AMOUNT)='0.00'\n" +
                "THEN repayment.total\n" +
                "ELSE\n" +
                "repayment.total - SUM(REPAYMENT_AMOUNT)\n" +
                "END AS surplusAmount,\n" +
                "over.allRemaring,\n" +
                " over.allOverDue\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tCASE\n" +
                "\t\tWHEN TYPE = 0 THEN\n" +
                "\t\t\tTOTAL\n" +
                "\t\tWHEN TYPE = 1 THEN\n" +
                "\t\t\tTOTAL\n" +
                "\t\tWHEN TYPE = 2 THEN\n" +
                "\t\t\tCONTRACT_REPAYMENT_AMOUNT\n" +
                "\t\tWHEN TYPE = 3 THEN\n" +
                "\t\t\tTOTAL\n" +
                "\t\tWHEN TYPE = 4 THEN\n" +
                "\t\t\tCONTRACT_REPAYMENT_AMOUNT\n" +
                "\t\tEND AS total,\n" +
                "\t\tREPAYMENT_AMOUNT,PRINCIPAL as principal,MAIN_ID AS mainId\n" +
                "\tFROM\n" +
                "\t\tt_con_repayment\n" +
                "\tWHERE\n" +
                "\t\tREPAYMENT_DATE = '" + repayDate + "'\n" +
                "\tAND MAIN_ID = '" + mainId + "'\n" +
                "\t) AS repayment \n";
        sql+=" LEFT JOIN (\tSELECT\n" +
                "\tSUM(a.allOverDue) AS allOverDue,\n" +
                "\tSUM(a.allRemaring) AS allRemaring,\n" +
                "\ta.mainId\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tsum(REMAINING_ARREARS) AS allRemaring,\n" +
                "\t\t\tsum(OVERDUE_PENALTY) AS allOverDue,\n" +
                "\t\t\tMAIN_ID AS mainId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_con_repayment\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND MAIN_ID = '"+mainId+"'\n" +
                "\t\tAND REPAYMENT_DATE < '"+repayDate+"'\n" +
                "\t\tUNION ALL\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tsum(REMAINING_ARREARS) AS allRemaring,\n" +
                "\t\t\t\tsum(JR_OVERDUE_PENALTY) AS allOverDue,\n" +
                "\t\t\t\tMAIN_ID AS mainId\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_con_financingment\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND MAIN_ID = '"+mainId+"'\n" +
                "\t\t\tAND t_con_financingment.FINANCING_DATE < '"+repayDate+"'\n" +
                "\t) AS a) AS over ON repayment.mainId = over.mainId";
        return queryForJsonObject(sql);
    }

    public List<JSONObject> getRepaymentHistory(String mainId) {
        String sql = "SELECT\n" +
                "\tACTUAL_REPAYMENT_DATE AS availableDate,\n" +
                "\tREPAYMENT_AMOUNT AS availableMoney\n" +
                "FROM\n" +
                "\tt_con_repayment\n" +
                "WHERE\n" +
                "\tMAIN_ID = '"+mainId+"'\n" +
                "AND ACTUAL_REPAYMENT_DATE IS NOT NULL\n" +
                "AND REPAYMENT_AMOUNT != '0.00'\n" +
                "ORDER BY\n" +
                "\tACTUAL_REPAYMENT_DATE DESC";
        return queryForJsonList(sql);
    }
    public List<JSONObject> getFinancingHistory(String mainId) {
        String sql = "SELECT * FROM(\n" +
                "SELECT\n" +
                "\tACTUAL_REPAYMENT_DATE AS availableDate,\n" +
                "\tREPAYMENT_AMOUNT AS availableMoney\n" +
                "FROM\n" +
                "\tt_con_repayment\n" +
                "WHERE\n" +
                "\tMAIN_ID = '"+mainId+"'\n" +
                "AND ACTUAL_REPAYMENT_DATE IS NOT NULL\n" +
                "AND REPAYMENT_AMOUNT != '0.00'\n" +
                "AND TYPE NOT IN('2','4')\n" +
                "UNION ALL\n" +
                "SELECT\n" +
                "\tADVANCES_DATE AS availableDate,\n" +
                "\tADVANCES_MONEY AS availableMoney\n" +
                "FROM\n" +
                "\tt_con_financingment\n" +
                "WHERE\n" +
                "\tMAIN_ID = '"+mainId+"'\n" +
                "AND ADVANCES_DATE IS NOT NULL\n" +
                "AND ADVANCES_MONEY != '0.00') AS repayment\n" +
                "ORDER BY\n" +
                "\trepayment.availableDate DESC";
        return queryForJsonList(sql);
    }

    public List<RepaymentEntity> getRepaymentEntity(String mainId, Integer type) {
        String sql = "SELECT\n" +
                "\tt1.*\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t\"\" AS id,\n" +
                "\t\t\t\"\" AS repaymentDate,\n" +
                "\t\t\t\"\" AS actualRepaymentDate,\n" +
                "\t\t\t\"0.00\" AS contractRepaymentAmount,\n" +
                "\t\t\t\"0\" AS haveSettle,\n" +
                "\t\t\t\"\" AS repaymentId,\n" +
                "\t\t\t\"\" AS interest,\n" +
                "\t\t\tt_con_financingment.MAIN_ID AS mainId,\n" +
                "\t\t\t\"\" AS overDuePenalty,\n" +
                "\t\t\t\"0\" AS parentId,\n" +
                "\t\t\t\"\" AS princiPal,\n" +
                "\t\t\t\"0.00\" AS remainingArrears,\n" +
                "\t\t\t\"\" AS remark,\n" +
                "\t\t\t\"0.00\" AS repaymentAmount,\n" +
                "\t\t\t\"\" AS repaymentAttachName,\n" +
                "\t\t\t\"\" AS repaymentAttachUrl,\n" +
                "\t\t\t\"\" AS sortNo,\n" +
                "\t\t\t\"\" AS total,\n" +
                "\t\t\t " + type + " AS type,\n" +
                "\t\t\tIFNULL(t_con_financingment.FINANCING_DATE,'') AS financingDate,\n" +
                "\t\t\tIFNULL(t_con_financingment.FINANCING_MONEY,'0.00') AS financingMoney,\n" +
                "\t\t\tIFNULL(t_con_financingment.ADVANCES_DATE,'') AS advancesDate,\n" +
                "\t\t\tIFNULL(t_con_financingment.ADVANCES_MONEY,'0.00') AS advancesMoney\n" +
                "\t\tFROM\n" +
                "\t\t\tt_con_financingment\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_con_financingment.FINANCING_DATE < (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_con_repayment\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tMAIN_ID = '" + mainId + "'\n" +
                "\t\t\t\tAND TYPE = " + type + "\n" +
                "\t\t\t\tAND PARENT_ID = 0\n" +
                "\t\t\t\tORDER BY\n" +
                "\t\t\t\t\tSORT_NO\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tAND MAIN_ID = '" + mainId + "'\n" +
                "\t\tORDER BY\n" +
                "\t\t\tSORT_NO\n" +
                "\t\tLIMIT 999999999\n" +
                "\t) AS t1\n" +
                "UNION ALL\n" +
                "\tSELECT\n" +
                "\t\tt2.*\n" +
                "\tFROM\n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_con_repayment.ID AS id,\n" +
                "\t\t\t\tt_con_repayment.REPAYMENT_DATE AS repaymentDate,\n" +
                "\t\t\t\tIFNULL(t_con_repayment.ACTUAL_REPAYMENT_DATE,'') AS actualRepaymentDate,\n" +
                "\t\t\t\tt_con_repayment.CONTRACT_REPAYMENT_AMOUNT AS contractRepaymentAmount,\n" +
                "\t\t\t\tt_con_repayment.HAVE_SETTLE AS haveSettle,\n" +
                "\t\t\t\tt_con_repayment.ID AS repaymentId,\n" +
                "\t\t\t\tt_con_repayment.INTEREST AS interest,\n" +
                "\t\t\t\tt_con_repayment.MAIN_ID AS mainId,\n" +
                "\t\t\t\tt_con_repayment.OVERDUE_PENALTY AS overDuePenalty,\n" +
                "\t\t\t\tt_con_repayment.PARENT_ID AS parentId,\n" +
                "\t\t\t\tt_con_repayment.PRINCIPAL AS princiPal,\n" +
                "\t\t\t\tt_con_repayment.REMAINING_ARREARS AS remainingArrears,\n" +
                "\t\t\t\tIFNULL(t_con_repayment.REMARK,'') AS remark,\n" +
                "\t\t\t\tt_con_repayment.REPAYMENT_AMOUNT AS repaymentAmount,\n" +
                "\t\t\t\tIFNULL(t_con_repayment.REPAYMENT_ATTACH_NAME,'') AS repaymentAttachName,\n" +
                "\t\t\t\tIFNULL(t_con_repayment.REPAYMENT_ATTACH_URL,'') AS repaymentAttachUrl,\n" +
                "\t\t\t\tt_con_repayment.SORT_NO AS sortNo,\n" +
                "\t\t\t\tt_con_repayment.TOTAL AS total,\n" +
                "\t\t\t\tt_con_repayment.TYPE AS type,\n" +
                "\t\t\t\tIFNULL(t_con_financingment.FINANCING_DATE,'') AS financingDate,\n" +
                "\t\t\tIFNULL(t_con_financingment.FINANCING_MONEY,'0.00') AS financingMoney,\n" +
                "\t\t\tIFNULL(t_con_financingment.ADVANCES_DATE,'') AS advancesDate,\n" +
                "\t\t\tIFNULL(t_con_financingment.ADVANCES_MONEY,'0.00') AS advancesMoney\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_con_repayment\n" +
                "\t\t\tLEFT JOIN t_con_financingment ON t_con_repayment.REPAYMENT_DATE = t_con_financingment.FINANCING_DATE\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND t_con_repayment.TYPE = " + type + "\n" +
                "\t\t\tAND t_con_repayment.PARENT_ID = 0\n" +
                "\t\t\tAND t_con_repayment.MAIN_ID = '" + mainId + "'\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\tt_con_financingment.MAIN_ID = '" + mainId + "'\n" +
                "\t\t\t\tOR t_con_financingment.MAIN_ID IS NULL\n" +
                "\t\t\t)\n" +
                "\t\t\tORDER BY\n" +
                "\t\t\t\tt_con_repayment.SORT_NO\n" +
                "\t\t\tLIMIT 999999999\n" +
                "\t\t) AS t2\n" +
                "\tUNION ALL\n" +
                "\t\tSELECT\n" +
                "\t\t\tt3.*\n" +
                "\t\tFROM\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\"\" AS id,\n" +
                "\t\t\t\t\t\"\" AS repaymentDate,\n" +
                "\t\t\t\t\t\"\" AS actualRepaymentDate,\n" +
                "\t\t\t\t\t\"0.00\" AS contractRepaymentAmount,\n" +
                "\t\t\t\t\t\"0\" AS haveSettle,\n" +
                "\t\t\t\t\t\"\" AS repaymentId,\n" +
                "\t\t\t\t\t\"\" AS interest,\n" +
                "\t\t\t\t\tt_con_financingment.MAIN_ID AS mainId,\n" +
                "\t\t\t\t\t\"\" AS overDuePenalty,\n" +
                "\t\t\t\t\t\"0\" AS parentId,\n" +
                "\t\t\t\t\t\"\" AS princiPal,\n" +
                "\t\t\t\t\t\"0.00\" AS remainingArrears,\n" +
                "\t\t\t\t\t\"\" AS remark,\n" +
                "\t\t\t\t\t\"0.00\" AS repaymentAmount,\n" +
                "\t\t\t\t\t\"\" AS repaymentAttachName,\n" +
                "\t\t\t\t\t\"\" AS repaymentAttachUrl,\n" +
                "\t\t\t\t\t\"\" AS sortNo,\n" +
                "\t\t\t\t\t\"\" AS total,\n" +
                "\t\t\t\t\t " + type + " AS type,\n" +
                "\t\t\t\t\tIFNULL(t_con_financingment.FINANCING_DATE,'') AS financingDate,\n" +
                "\t\t\tIFNULL(t_con_financingment.FINANCING_MONEY,'0.00') AS financingMoney,\n" +
                "\t\t\tIFNULL(t_con_financingment.ADVANCES_DATE,'') AS advancesDate,\n" +
                "\t\t\tIFNULL(t_con_financingment.ADVANCES_MONEY,'0.00') AS advancesMoney\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_con_financingment\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tt_con_financingment.FINANCING_DATE > (\n" +
                "\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\tt_con_repayment.REPAYMENT_DATE\n" +
                "\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\tt_con_repayment\n" +
                "\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\tMAIN_ID = '" + mainId + "'\n" +
                "\t\t\t\t\t\tAND TYPE = " + type + "\n" +
                "\t\t\t\t\t\tAND PARENT_ID = 0\n" +
                "\t\t\t\t\t\tORDER BY\n" +
                "\t\t\t\t\t\t\tSORT_NO DESC\n" +
                "\t\t\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t\t\t1\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tAND MAIN_ID = '" + mainId + "'\n" +
                "\t\t\t\tORDER BY\n" +
                "\t\t\t\t\tSORT_NO\n" +
                "\t\t\t\tLIMIT 999999999\n" +
                "\t\t\t) AS t3";
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString());
        List<RepaymentEntity> entityList = new ArrayList<>();
        if (jsonList != null && jsonList.size() > 0) {
            for (int i = 0; i < jsonList.size(); i++) {
                RepaymentEntity iBaseEntity = (RepaymentEntity) convertJson2Entity(jsonList.get(i));
                entityList.add(iBaseEntity);
            }
        }
        return entityList;
    }
}
